
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.*;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import javax.swing.JFileChooser;

 /******************************************************************************
 * File:NewJFrame.java
 * Course: 17655
 * Project: Assignment 2
 * Copyright: Copyright (c) 2013 Carnegie Mellon University
 * Versions:
 *	1.0 February 2013 - Creation of form for log retrieve (luisrocha)
 *
 * This class defines a GUI application that allows EEP manager personnel to
 * generate and retrieve log files.
 *
 ******************************************************************************/
/*
 * Created on Feb 24, 2013
 *
 * @author luisrocha
 */
public class NewJFrame extends javax.swing.JFrame {
    
    String versionID = "v1.10.00";
    String userId;
    
    /** Creates new form NewJFrame */
    public NewJFrame(String DbIP, String UserID) {
        initComponents();
        jLabel1.setText("Log Retrieve Application " + versionID);
        userId = UserID;
        jTextField5.setText(userId);
        jTextField1.setText(DbIP);
        
        //initialize the date interval for the logs
        //create the end date from the current time
        Calendar TimeStamp = Calendar.getInstance();
        //create the start date one month before current date
	Calendar TimeStamp1monthago = (Calendar) TimeStamp.clone();
        TimeStamp1monthago.add(Calendar.MONTH, -1);
        SimpleDateFormat TimeStampFormat = new SimpleDateFormat("MM/dd/yyyy");
        jTextField3.setText(TimeStampFormat.format(TimeStamp.getTime()));
        jTextField2.setText(TimeStampFormat.format(TimeStamp1monthago.getTime()));       
        
    }

    /** This method is called from within the constructor to
     * initialize the form.
     * WARNING: Do NOT modify this code. The content of this method is
     * always regenerated by the Form Editor.
     */
    @SuppressWarnings("unchecked")
    // <editor-fold defaultstate="collapsed" desc="Generated Code">//GEN-BEGIN:initComponents
    private void initComponents() {

        jLabel1 = new javax.swing.JLabel();
        jLabel3 = new javax.swing.JLabel();
        jTextField1 = new javax.swing.JTextField();
        jButton2 = new javax.swing.JButton();
        jLabel11 = new javax.swing.JLabel();
        jScrollPane4 = new javax.swing.JScrollPane();
        jTextArea4 = new javax.swing.JTextArea();
        jTextField3 = new javax.swing.JTextField();
        jTextField2 = new javax.swing.JTextField();
        jLabel12 = new javax.swing.JLabel();
        jLabel13 = new javax.swing.JLabel();
        jButton1 = new javax.swing.JButton();
        jButton3 = new javax.swing.JButton();
        jTextField4 = new javax.swing.JTextField();
        jLabel14 = new javax.swing.JLabel();
        jButton4 = new javax.swing.JButton();
        jLabel4 = new javax.swing.JLabel();
        jTextField5 = new javax.swing.JTextField();
        jLabel15 = new javax.swing.JLabel();

        setDefaultCloseOperation(javax.swing.WindowConstants.EXIT_ON_CLOSE);
        addWindowListener(new java.awt.event.WindowAdapter() {
            public void windowClosing(java.awt.event.WindowEvent evt) {
                formWindowClosing(evt);
            }
        });

        jLabel1.setText("Log Retrieve Application");

        jLabel3.setText("Server IP Address:");

        jTextField1.setText("localhost");

        jButton2.setText("Order Submission History");
        jButton2.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jButton2ActionPerformed(evt);
            }
        });

        jLabel11.setText("Messages");

        jTextArea4.setColumns(20);
        jTextArea4.setEditable(false);
        jTextArea4.setRows(5);
        jScrollPane4.setViewportView(jTextArea4);

        jTextField3.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jTextField3ActionPerformed(evt);
            }
        });

        jTextField2.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jTextField2ActionPerformed(evt);
            }
        });

        jLabel12.setText("Log Start Date (MM/dd/yyyy)");

        jLabel13.setText("Log End Date (MM/dd/yyyy)");

        jButton1.setText("User Login History");
        jButton1.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jButton1ActionPerformed(evt);
            }
        });

        jButton3.setText("Order Shipping History");
        jButton3.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jButton3ActionPerformed(evt);
            }
        });

        jTextField4.setText("Log.txt");
        jTextField4.setToolTipText("Filename to store the selected log");
        jTextField4.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jTextField4ActionPerformed(evt);
            }
        });

        jLabel14.setText("Filename");

        jButton4.setText("Save Filename...");
        jButton4.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jButton4ActionPerformed(evt);
            }
        });

        jLabel4.setText("UserID:");

        jTextField5.setEditable(false);
        jTextField5.setText("UserID");

        jLabel15.setText("Filter dates in the log (only applies to order submission or order shipping history):");

        javax.swing.GroupLayout layout = new javax.swing.GroupLayout(getContentPane());
        getContentPane().setLayout(layout);
        layout.setHorizontalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(layout.createSequentialGroup()
                .addGap(300, 300, 300)
                .addComponent(jLabel1)
                .addGap(0, 0, Short.MAX_VALUE))
            .addGroup(layout.createSequentialGroup()
                .addGap(45, 45, 45)
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addGroup(layout.createSequentialGroup()
                        .addComponent(jLabel11)
                        .addContainerGap(javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE))
                    .addGroup(javax.swing.GroupLayout.Alignment.TRAILING, layout.createSequentialGroup()
                        .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.TRAILING)
                            .addGroup(javax.swing.GroupLayout.Alignment.LEADING, layout.createSequentialGroup()
                                .addComponent(jTextField4)
                                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                                .addComponent(jButton4))
                            .addGroup(layout.createSequentialGroup()
                                .addComponent(jButton1, javax.swing.GroupLayout.PREFERRED_SIZE, 208, javax.swing.GroupLayout.PREFERRED_SIZE)
                                .addGap(18, 18, 18)
                                .addComponent(jButton2)
                                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                                .addComponent(jButton3, javax.swing.GroupLayout.PREFERRED_SIZE, 206, javax.swing.GroupLayout.PREFERRED_SIZE))
                            .addComponent(jScrollPane4, javax.swing.GroupLayout.Alignment.LEADING)
                            .addGroup(javax.swing.GroupLayout.Alignment.LEADING, layout.createSequentialGroup()
                                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                                    .addComponent(jLabel12)
                                    .addComponent(jTextField2, javax.swing.GroupLayout.PREFERRED_SIZE, 295, javax.swing.GroupLayout.PREFERRED_SIZE)
                                    .addGroup(layout.createSequentialGroup()
                                        .addComponent(jLabel4)
                                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                                        .addComponent(jTextField5, javax.swing.GroupLayout.DEFAULT_SIZE, 265, Short.MAX_VALUE)))
                                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                                    .addGroup(layout.createSequentialGroup()
                                        .addGap(157, 157, 157)
                                        .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                                            .addComponent(jTextField3)
                                            .addGroup(layout.createSequentialGroup()
                                                .addComponent(jLabel13)
                                                .addGap(0, 21, Short.MAX_VALUE))))
                                    .addGroup(layout.createSequentialGroup()
                                        .addGap(73, 73, 73)
                                        .addComponent(jLabel3)
                                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                                        .addComponent(jTextField1)))))
                        .addGap(45, 45, 45))
                    .addGroup(layout.createSequentialGroup()
                        .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                            .addComponent(jLabel15)
                            .addComponent(jLabel14))
                        .addGap(0, 0, Short.MAX_VALUE))))
        );
        layout.setVerticalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(layout.createSequentialGroup()
                .addContainerGap()
                .addComponent(jLabel1)
                .addGap(25, 25, 25)
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                        .addComponent(jLabel4)
                        .addComponent(jTextField5, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE))
                    .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                        .addComponent(jLabel3)
                        .addComponent(jTextField1, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)))
                .addGap(33, 33, 33)
                .addComponent(jLabel15)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(jLabel12)
                    .addComponent(jLabel13))
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(jTextField3, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(jTextField2, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE))
                .addGap(27, 27, 27)
                .addComponent(jLabel14)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(jTextField4, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(jButton4))
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED, 39, Short.MAX_VALUE)
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(jButton1, javax.swing.GroupLayout.PREFERRED_SIZE, 99, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(jButton3, javax.swing.GroupLayout.PREFERRED_SIZE, 99, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(jButton2, javax.swing.GroupLayout.PREFERRED_SIZE, 99, javax.swing.GroupLayout.PREFERRED_SIZE))
                .addGap(41, 41, 41)
                .addComponent(jLabel11)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED)
                .addComponent(jScrollPane4, javax.swing.GroupLayout.PREFERRED_SIZE, 110, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addGap(32, 32, 32))
        );

        pack();
    }// </editor-fold>//GEN-END:initComponents

    private void jButton2ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton2ActionPerformed
        // This method is responsible to retrieve and save the log of
        // orders created.

        Boolean connectError = false;       // Error flag
        Connection DBConn = null;           // MySQL connection handle
        String errString = null;            // String for displaying errors
        String msgString = null;            // String for displaying non-error messages
        String recString = null;            // String for file output record
        ResultSet res = null;               // SQL query result set pointer
        Statement s = null;                 // SQL statement pointer
        String SQLStatement = null;         // SQL statement string
   
	SimpleDateFormat TimeStampFormat = new SimpleDateFormat("MM/dd/yyyy HH:mm:ss");
        Date StartLogDate = null;
        Date EndLogDate = null;
        //create a time stamp of current time and one month ago in case an error occurs in filter dates
	Calendar TimeStamp = Calendar.getInstance();
        Calendar TimeStamp1monthago = (Calendar) TimeStamp.clone();
        TimeStamp1monthago.add(Calendar.MONTH, -1);

        FileWriter outputFileWriter = null;
	File file = null;
	//header of the logfile format
	String Header1 = "Order:     Username:                 Time and Date Created:  \n";
	String Header2 = "-------------------------------------------------------------\n";	
        String outputFileName = "SalesOrderLog.txt";
        
        // Connect to the order database
        try
        {
            msgString = ">> Establishing Driver...";
            jTextArea4.setText("\n"+msgString);

            //load JDBC driver class for MySQL
            Class.forName( "com.mysql.jdbc.Driver" );

            msgString = ">> Setting up URL...";
            jTextArea4.append("\n"+msgString);

            //define the data source
            String SQLServerIP = jTextField1.getText();
            String sourceURL = "jdbc:mysql://" + SQLServerIP + ":3306/orderinfo";

            msgString = ">> Establishing connection with: " + sourceURL + "...";
            jTextArea4.append("\n"+msgString);

            //create a connection to the db - note the default account is "remote"
            //and the password is "remote_pass" - you will have to set this
            //account up in your database
            DBConn = DriverManager.getConnection(sourceURL,"remote","remote_pass");

        } catch (Exception e) {

            errString =  "\nProblem connecting to orderinfo database:: " + e;
            jTextArea4.append(errString);
            connectError = true;

        } // end try-catch

        // If we are connected, then we retrieve orders created

        if ( !connectError )
        {
            
            //open log file and write header
            try 
		{
			outputFileName = jTextField4.getText();
                        file = new File(outputFileName);
			outputFileWriter = new FileWriter(file);
			outputFileWriter.write(Header1);
			outputFileWriter.write(Header2);
			
		} // try
		catch (IOException e) 
		{
			System.out.println(this.getName() + "FileNotFoundException occured; " + outputFileName + "not found" + e.getMessage());
		} // catch
            
            
        
            try
            {
              
               //parse start log date 
               try {
                StartLogDate = (Date) TimeStampFormat.parse(jTextField2.getText() + " 00:00:00");
               }
               catch (ParseException e) {
                   msgString +=  "\nLog Start Date cannot be interpreted, using one month ago...";
                   jTextArea4.setText(msgString);
                   StartLogDate = TimeStamp1monthago.getTime();             
               }
                
               //parse end log date 
               try {
                EndLogDate = (Date) TimeStampFormat.parse(jTextField3.getText() + " 23:59:59");
               }
               catch (ParseException e) {
                   msgString +=  "\nLog End Date cannot be interpreted, using today...";
                   jTextArea4.setText(msgString);
                   EndLogDate = TimeStamp.getTime();             
               }
           
               msgString +=  "\nStarting to read orders filtering timecreated <= " + 
                       TimeStampFormat.format(EndLogDate) + " ( " + EndLogDate.getTime() +
                       " )... \n...and timecreated >= " + TimeStampFormat.format(StartLogDate) +
                       " ( " + StartLogDate.getTime() + " )";
               jTextArea4.setText(msgString); 
               
                // first we create the query
                s = DBConn.createStatement();
                SQLStatement = "SELECT * FROM orders " +
                        " WHERE timecreated <= " + EndLogDate.getTime() +
                        " AND timecreated >= " + StartLogDate.getTime();
                
                // execute the statement
                res = s.executeQuery( SQLStatement );

                // if the query worked, then we display the data in TextArea 4
                
                while (res.next())
                {
   
		   TimeStamp.setTimeInMillis(res.getLong("timecreated"));
	                  
                   msgString += "\nORDER # " + res.getString(1) + " : " + res.getString(2) +
                             " : "+ res.getString(3) + " : " + res.getString(4) +
                             " : "+ TimeStampFormat.format(TimeStamp.getTime()) +
                             " : " + res.getString(12);
                  
                   recString = String.format("%-10s",res.getString(1)) + " " +
                               String.format("%-25s",res.getString(12)) + " " +
                               TimeStampFormat.format(TimeStamp.getTime()) + "\n";
                   
                  //write to outputfile
                  outputFileWriter.write(recString);
                } // while

                // notify the user all went well and enable the select order
                // button
                msgString +=  "\nORDERS RETRIEVED...";
                jTextArea4.setText(msgString);

              
                

            } catch (Exception e) {

                errString =  "\nProblem retrieving orders:: " + e;
                jTextArea4.append(errString);


            } // end try-catch

            
            try {
                    //close file after end of stream
                    outputFileWriter.close();
                    msgString +=  "\nLog File Saved in" + file.getAbsolutePath();
                    jTextArea4.setText(msgString);
                } // try 
		catch (IOException e) 
		{
                    System.out.println(this.getName() + ": File close exception: " + e.getMessage());
		} // catch
            
            
        } // if connect check

    }//GEN-LAST:event_jButton2ActionPerformed

    private void jTextField3ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jTextField3ActionPerformed
        // TODO add your handling code here:
    }//GEN-LAST:event_jTextField3ActionPerformed

    private void jTextField2ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jTextField2ActionPerformed
        // TODO add your handling code here:
    }//GEN-LAST:event_jTextField2ActionPerformed

    private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton1ActionPerformed
        // This method is responsible to retrieve and save the log of
        // user logins and logouts.

        Boolean connectError = false;       // Error flag
        Connection DBConn = null;           // MySQL connection handle
        String errString = null;            // String for displaying errors
        String msgString = null;            // String for displaying non-error messages
        String recString = null;            // String for file output record
        ResultSet res = null;               // SQL query result set pointer
        Statement s = null;                 // SQL statement pointer
        String SQLStatement = null;         // SQL statement string
   
	SimpleDateFormat TimeStampFormat = new SimpleDateFormat("MM/dd/yyyy HH:mm:ss");
        Date StartLogDate = null;
        Date EndLogDate = null;
        //create a time stamp of current time and one month ago in case an error occurs in filter dates
	Calendar TimeStamp = Calendar.getInstance();
        Calendar TimeStamp1monthago = (Calendar) TimeStamp.clone();
        TimeStamp1monthago.add(Calendar.MONTH, -1);

        FileWriter outputFileWriter = null;
	File file = null;
	//header of the logfile format
	String Header1 = "Username:                 Action:                   Date/Time:               \n";
	String Header2 = "-----------------------------------------------------------------------------\n";	
        String outputFileName = "UserActionLog.txt";
        
        // Connect to the order database
        try
        {
            msgString = ">> Establishing Driver...";
            jTextArea4.setText("\n"+msgString);

            //load JDBC driver class for MySQL
            Class.forName( "com.mysql.jdbc.Driver" );

            msgString = ">> Setting up URL...";
            jTextArea4.append("\n"+msgString);

            /*
             * TODO CHANGE TO USER LOG DATABASE
             */
            //define the data source
            String SQLServerIP = jTextField1.getText();
            String sourceURL = "jdbc:mysql://" + SQLServerIP + ":3306/orderinfo";

            msgString = ">> Establishing connection with: " + sourceURL + "...";
            jTextArea4.append("\n"+msgString);

            //create a connection to the db - note the default account is "remote"
            //and the password is "remote_pass" - you will have to set this
            //account up in your database
            DBConn = DriverManager.getConnection(sourceURL,"remote","remote_pass");

        } catch (Exception e) {

            errString =  "\nProblem connecting to user database:: " + e;
            jTextArea4.append(errString);
            connectError = true;

        } // end try-catch

        // If we are connected, then we retrieve user log

        if ( !connectError )
        {
            
            //open log file and write header
            try 
		{
			outputFileName = jTextField4.getText();
                        file = new File(outputFileName);
			outputFileWriter = new FileWriter(file);
			outputFileWriter.write(Header1);
			outputFileWriter.write(Header2);
			
		} // try
		catch (IOException e) 
		{
			System.out.println(this.getName() + "FileNotFoundException occured; " + outputFileName + "not found" + e.getMessage());
		} // catch
            
            
        
            try
            {
              
               //parse start log date 
               try {
                StartLogDate = TimeStampFormat.parse(jTextField2.getText() + " 00:00:00");
               }
               catch (ParseException e) {
                   msgString +=  "\nLog Start Date cannot be interpreted, using one month ago...";
                   jTextArea4.setText(msgString);
                   StartLogDate = TimeStamp1monthago.getTime();             
               }
                
               //parse end log date 
               try {
                EndLogDate = TimeStampFormat.parse(jTextField3.getText() + " 23:59:59");
               }
               catch (ParseException e) {
                   msgString +=  "\nLog End Date cannot be interpreted, using today...";
                   jTextArea4.setText(msgString);
                   EndLogDate = TimeStamp.getTime();             
               }
           
               //msgString +=  "\nStarting to read user actions filtering last_login_time <= " + 
               //        TimeStampFormat.format(EndLogDate) + " ( " + EndLogDate.getTime() +
               //       " )... \n...and last_login_time >= " + TimeStampFormat.format(StartLogDate) +
               //        " ( " + StartLogDate.getTime() + " )";
               //jTextArea4.setText(msgString); 
               
                // first we create the query
                msgString +=  "\nquerying app_users table...";
               jTextArea4.setText(msgString); 
               
                s = DBConn.createStatement();
                SQLStatement = "SELECT * FROM user_log ";
                    //" WHERE last_login_time <= " + TimeStampFormat.format(EndLogDate) +
                    //" AND last_login_time >= " + TimeStampFormat.format(StartLogDate);
                
                // execute the statement
                res = s.executeQuery( SQLStatement );

                // if the query worked, then we display the data in TextArea 4
                
                while (res.next())
                {
               
                   msgString += "\nUSER " + res.getString(1) + " : " + res.getString(2) +
                             " : "+ res.getString(3) + " : " + res.getString(4);
                  
                   recString = String.format("%-25s",res.getString(1)) + " " +                           
                               String.format("%-25s",res.getString(3)) + " " +
                               String.format("%-25s",res.getString(4)) + "\n";
 
                  //write to outputfile
                  outputFileWriter.write(recString);
                } // while

                // notify the user all went well and enable the select order
                // button
                msgString +=  "\nUSER ACTIONS RETRIEVED...";
                jTextArea4.setText(msgString);

            } catch (Exception e) {

                errString =  "\nProblem retrieving user actions:: " + e;
                jTextArea4.append(errString);

            } // end try-catch
            
            try {
                    //close file after end of stream
                    outputFileWriter.close();
                    msgString +=  "\nLog File Saved in" + file.getAbsolutePath();
                    jTextArea4.setText(msgString);
                } // try 
		catch (IOException e) 
		{
                    System.out.println(this.getName() + ": File close exception: " + e.getMessage());
		} // catch
            
            
        } // if connect check

        
        
        
        
        
        // TODO add your handling code here:
    }//GEN-LAST:event_jButton1ActionPerformed

    private void jButton3ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton3ActionPerformed

        // This method is responsible to retrieve and save the log of
        // shippments.

        Boolean connectError = false;       // Error flag
        Connection DBConn = null;           // MySQL connection handle
        String errString = null;            // String for displaying errors
        String msgString = null;            // String for displaying non-error messages
        String recString = null;            // String for file output record
        ResultSet res = null;               // SQL query result set pointer
        Statement s = null;                 // SQL statement pointer
        String SQLStatement = null;         // SQL statement string

        SimpleDateFormat TimeStampFormat = new SimpleDateFormat("MM/dd/yyyy HH:mm:ss");
        Date StartLogDate = null;
        Date EndLogDate = null;
        //create a time stamp of current time and one month ago in case an error occurs in filter dates
	Calendar TimeStamp = Calendar.getInstance();
        Calendar TimeStamp1monthago = (Calendar) TimeStamp.clone();
        TimeStamp1monthago.add(Calendar.MONTH, -1);
        
        FileWriter outputFileWriter = null;
	File file = null;
	//header of the logfile format
	String Header1 = "Order:     Time and Date Shipped:  \n";
	String Header2 = "-----------------------------------\n";	
        String outputFileName = "ShippingLog.txt";
        
        // Connect to the order database
        try
        {
            msgString = ">> Establishing Driver...";
            jTextArea4.setText("\n"+msgString);

            //load JDBC driver class for MySQL
            Class.forName( "com.mysql.jdbc.Driver" );

            msgString = ">> Setting up URL...";
            jTextArea4.append("\n"+msgString);

            //define the data source
            String SQLServerIP = jTextField1.getText();
            String sourceURL = "jdbc:mysql://" + SQLServerIP + ":3306/orderinfo";

            msgString = ">> Establishing connection with: " + sourceURL + "...";
            jTextArea4.append("\n"+msgString);

            //create a connection to the db - note the default account is "remote"
            //and the password is "remote_pass" - you will have to set this
            //account up in your database
            DBConn = DriverManager.getConnection(sourceURL,"remote","remote_pass");

        } catch (Exception e) {

            errString =  "\nProblem connecting to orderinfo database:: " + e;
            jTextArea4.append(errString);
            connectError = true;

        } // end try-catch

        // If we are connected, then we retrieve orders created

        if ( !connectError )
        {
            
            //open log file and write header
            try 
		{
			outputFileName = jTextField4.getText();
                        file = new File(outputFileName);
			outputFileWriter = new FileWriter(file);
			outputFileWriter.write(Header1);
			outputFileWriter.write(Header2);
			
		} // try
		catch (IOException e) 
		{
			System.out.println(this.getName() + "FileNotFoundException occured; " + outputFileName + "not found" + e.getMessage());
		} // catch
            
            
        
            try
            {
               //parse start log date 
               try {
                StartLogDate = (Date) TimeStampFormat.parse(jTextField2.getText() + " 00:00:00");
               }
               catch (ParseException e) {
                   msgString +=  "\nLog Start Date cannot be interpreted, using one month ago...";
                   jTextArea4.setText(msgString);
                   StartLogDate = TimeStamp1monthago.getTime();             
               }
                
               //parse end log date 
               try {
                EndLogDate = (Date) TimeStampFormat.parse(jTextField3.getText() + " 23:59:59");
               }
               catch (ParseException e) {
                   msgString +=  "\nLog End Date cannot be interpreted, using today...";
                   jTextArea4.setText(msgString);
                   EndLogDate = TimeStamp.getTime();             
               }
               
               msgString +=  "\nStarting to read orders filtering timeshipped <= " + 
                       TimeStampFormat.format(EndLogDate) + " ( " + EndLogDate.getTime() +
                       " )... \n...and timeshipped >= " + TimeStampFormat.format(StartLogDate) +
                       " ( " + StartLogDate.getTime() + " )";
               jTextArea4.setText(msgString); 
                
                
                
                // first we create the query
                s = DBConn.createStatement();
                SQLStatement = "SELECT * FROM orders " +
                        " WHERE timeshipped <= " + EndLogDate.getTime() +
                        " AND timeshipped >= " + StartLogDate.getTime();
                
                // execute the statement
                res = s.executeQuery( SQLStatement );

                // if the query worked, then we display the data in TextArea 4
                
                while (res.next())
                {
   
		   TimeStamp.setTimeInMillis(res.getLong("timeshipped"));
	                  
                   msgString += "\nORDER # " + res.getString(1) + " : " + res.getString(2) +
                             " : "+ res.getString(3) + " : " + res.getString(4) +
                             " : "+ TimeStampFormat.format(TimeStamp.getTime()) +
                             " : " + res.getString(12);
                  
                   recString = String.format("%-10s",res.getString(1)) + " " +
                               TimeStampFormat.format(TimeStamp.getTime()) + "\n";
                   
                  //write to outputfile
                  outputFileWriter.write(recString);
                } // while

                // notify the user all went well and enable the select order
                // button
                msgString +=  "\nSHIPPED ORDERS RETRIEVED...";
                jTextArea4.setText(msgString);

            } catch (Exception e) {

                errString =  "\nProblem retrieving orders:: " + e;
                jTextArea4.append(errString);


            } // end try-catch

            
            try {
                    //close file after end of stream
                    outputFileWriter.close();
                    msgString +=  "\nLog File Saved in" + file.getAbsolutePath();
                    jTextArea4.setText(msgString);
                } // try 
		catch (IOException e) 
		{
                    System.out.println(this.getName() + ": File close exception: " + e.getMessage());
		} // catch
            
            
        } // if connect check
 
    }//GEN-LAST:event_jButton3ActionPerformed

    private void jTextField4ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jTextField4ActionPerformed
        // TODO add your handling code here:
    }//GEN-LAST:event_jTextField4ActionPerformed

    private void jButton4ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton4ActionPerformed
        
        //file chooser code following example in http://docs.oracle.com/javase/tutorial/uiswing/components/filechooser.html
        
        //Create a file chooser in order to allow to select a file to save to
        //start with current directory
        final JFileChooser fc = new JFileChooser(System.getProperty("user.dir"));

        //initialize the dialog
        int returnVal = fc.showSaveDialog(jTextField4);
        
        //if user selected a file then replace the text box with the full path of file to create
        if ( returnVal == JFileChooser.APPROVE_OPTION) 
        {
              jTextField4.setText(fc.getSelectedFile().getAbsolutePath());
        }
    }//GEN-LAST:event_jButton4ActionPerformed

    private void formWindowClosing(java.awt.event.WindowEvent evt) {//GEN-FIRST:event_formWindowClosing
        // TODO add your handling code here:
        Connection DBConn1 = null;
         Statement s1 = null;
         int ret;
         
         try
            {
                 String SQLServerIP = jTextField1.getText();
                 String sourceURL = "jdbc:mysql://" + SQLServerIP + ":3306/orderinfo";
                    DBConn1 = DriverManager.getConnection(sourceURL,"remote","remote_pass");
                s1 = DBConn1.createStatement();
                ret = s1.executeUpdate("UPDATE orderinfo.app_users set last_logout_time = CURRENT_TIMESTAMP where userid = \'" + userId + "\'" );
                //JTextErrorStr.setText("Select userid, passwd from app_users where userid = \" " + JTextUserId.getText() + "");
                ret = s1.executeUpdate("Commit");
                        
            } catch (Exception e) {

                  jTextArea4.append("Error Updating database before closing application " +"\n");
               
            }
    }//GEN-LAST:event_formWindowClosing

    /*
    /**
    * @param args the command line arguments
    */
    /*
    public static void main(String args[]) {
        java.awt.EventQueue.invokeLater(new Runnable() {
            public void run() {
                new NewJFrame().setVisible(true);
            }
        });
    }
    */

    // Variables declaration - do not modify//GEN-BEGIN:variables
    private javax.swing.JButton jButton1;
    private javax.swing.JButton jButton2;
    private javax.swing.JButton jButton3;
    private javax.swing.JButton jButton4;
    private javax.swing.JLabel jLabel1;
    private javax.swing.JLabel jLabel11;
    private javax.swing.JLabel jLabel12;
    private javax.swing.JLabel jLabel13;
    private javax.swing.JLabel jLabel14;
    private javax.swing.JLabel jLabel15;
    private javax.swing.JLabel jLabel3;
    private javax.swing.JLabel jLabel4;
    private javax.swing.JScrollPane jScrollPane4;
    private javax.swing.JTextArea jTextArea4;
    private javax.swing.JTextField jTextField1;
    private javax.swing.JTextField jTextField2;
    private javax.swing.JTextField jTextField3;
    private javax.swing.JTextField jTextField4;
    private javax.swing.JTextField jTextField5;
    // End of variables declaration//GEN-END:variables

}
